﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using SmoLiteApi.Sql;
using SmoLiteApi.Helper;
using System.Data;

namespace SmoLiteApi
{
    /// <summary/>
    public class TableCollection : IEnumerable<Table>
    {
        private Database _database;

        /// <summary/>
        internal TableCollection(Database database)
        {
            this._database = database;
        }

        /// <summary/>
        public Table this[string name, string schemaName]
        {
            get
            {
                // get the list
                List<Table> tables = this.FetchObjects(name, schemaName);

                // return the data
                if (tables.Count == 0)
                {
                    return null;
                }

                // return the one and only
                return tables[0];
            }
        }

        /// <summary/>
        public IEnumerator<Table> GetEnumerator()
        {
            return this.FetchObjects(null, null).GetEnumerator();
        }

        /// <summary/>
        IEnumerator IEnumerable.GetEnumerator()
        {
            return this.FetchObjects(null, null).GetEnumerator();
        }

        /// <summary/>
        private List<Table> FetchObjects(
            string name,
            string schemaName)
        {
            List<Table> tables = new List<Table>();

            TableSelectSql tableSelectSql = new TableSelectSql();

            // set attributes
            tableSelectSql.AddObject(new { Name = name, Schema = schemaName });

            using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._database.SqlConnection, tableSelectSql.Render()))
            {
                using (SqlDataReader sqlDataReader = sqlCommandHelper.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                        Table table = new Table(
                            this._database,
                            sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_NAME)),
                            sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_SCHEMA)));

                        // add to the list
                        tables.Add(table);
                    }
                }
            }

            TableColumnSelectSql tableColumnSelectSql = new TableColumnSelectSql();

            // set attributes
            tableColumnSelectSql.AddObject(new { Name = name, Schema = schemaName });

            using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._database.SqlConnection, tableColumnSelectSql.Render()))
            {
                using (SqlDataReader sqlDataReader = sqlCommandHelper.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                        Table table = tables.Find(
                            value => value.Name == sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_TABLE)));

                        Column column = new Column(
                            table,
                            sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_NAME)));

                        DataType dataType = column.DataType;

                        // set attributes
                        dataType.MaximumLength = sqlDataReader.GetInt16(sqlDataReader.GetOrdinal(Constant.PARAMETER_MAX_LENGTH));
                        dataType.NumericPrecision = sqlDataReader.GetByte(sqlDataReader.GetOrdinal(Constant.PARAMETER_PRECISION));
                        dataType.NumericScale = sqlDataReader.GetByte(sqlDataReader.GetOrdinal(Constant.PARAMETER_SCALE));
                        dataType.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_SQL_DB_TYPE)));
                        dataType.IsMax = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal(Constant.PARAMETER_IS_MAX));

                        // set attributes
                        column.Nullable = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal(Constant.PARAMETER_IS_NULLABLE));
                        column.Identity = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal(Constant.PARAMETER_IS_IDENTITY));

                        // add to the list
                        table.Columns.Add(column);
                    }
                }
            }

            TableForeignKeySelectSql tableForeignKeySelectSql = new TableForeignKeySelectSql();

            // set attributes
            tableForeignKeySelectSql.AddObject(new { Name = name, Schema = schemaName });

            using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._database.SqlConnection, tableForeignKeySelectSql.Render()))
            {
                using (SqlDataReader sqlDataReader = sqlCommandHelper.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                        Table table = tables.Find(value => 
                            value.Name == sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_TABLE)) &&
                            value.Schema == sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_SCHEMA)));

                        ForeignKey foreignKey = table.ForeignKeys[
                            sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_NAME))];

                        // has the key been created
                        if (foreignKey == null)
                        {
                            // create it
                            foreignKey = new ForeignKey(
                                sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_NAME)));

                            // set attributes
                            foreignKey.ReferencedTable = sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_REFERENCED_TABLE));
                            foreignKey.ReferencedTableSchema = sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_REFERENCED_SCHEMA));

                            // add it
                            table.ForeignKeys.Add(foreignKey);
                        }

                        ForeignKeyColumn foreignKeyColumn = new ForeignKeyColumn(
                            sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_COLUMN)));

                        // set attributes
                        foreignKeyColumn.ReferencedColumn = sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_REFERENCED_COLUMN));

                        // add to the list
                        foreignKey.Columns.Add(foreignKeyColumn);
                    }
                }
            }

            TableIndexSelectSql tableIndexSelectSql = new TableIndexSelectSql();

            // set attributes
            tableIndexSelectSql.AddObject(new { Name = name, Schema = schemaName });

            using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._database.SqlConnection, tableIndexSelectSql.Render()))
            {
                using (SqlDataReader sqlDataReader = sqlCommandHelper.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                        Table table = tables.Find(value =>
                            value.Name == sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_TABLE)) &&
                            value.Schema == sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_SCHEMA)));

                        Index index = table.Indexes[
                            sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_NAME))];

                        // has the key been created
                        if (index == null)
                        {
                            // create it
                            index = new Index(
                                sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_NAME)));

                            // set attributes
                            index.FillFactor = sqlDataReader.GetByte(sqlDataReader.GetOrdinal(Constant.PARAMETER_FILL_FACTOR));
                            index.IsPrimaryKey = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal(Constant.PARAMETER_IS_PRIMARY_KEY));
                            index.IsClustered = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal(Constant.PARAMETER_IS_CLUSTERED));
                            index.IsUnique = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal(Constant.PARAMETER_IS_UNIQUE));
                            index.PadIndex = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal(Constant.PARAMETER_IS_PADDED));

                            // add it
                            table.Indexes.Add(index);
                        }

                        IndexedColumn indexedColumn = new IndexedColumn(
                            sqlDataReader.GetByte(sqlDataReader.GetOrdinal(Constant.PARAMETER_KEY_ORDINAL)),
                            sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_COLUMN)));

                        // set attributes
                        indexedColumn.Descending = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal(Constant.PARAMETER_IS_DESCENDING_KEY));
                        indexedColumn.IsIncluded = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal(Constant.PARAMETER_IS_INCLUDED_COLUMN));

                        // add to the list
                        index.IndexedColumns.Add(indexedColumn);
                    }
                }
            }

            // return the list
            return tables;
        }
    }
}
